package com.home.andromotic;

import java.util.ArrayList;

import com.home.andromotic.model.IOIODevice;

import android.app.AlertDialog;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import android.util.Log;

public class AndromoticDbHelper extends SQLiteOpenHelper {

	static final String TAG = "ANDROMOTIC DataBases";
	static final String DB_NAME = "andromotic.db"; //
	static final int DB_VERSION = 5; //4

	static final String TABLE_PROFILES_DEVICES = "perfiles_devices"; // relacionados
	static final String TABLE_PROFILES = "perfiles"; // solo perfiles
	static final String TABLE_DEVICES = "dispositivos"; // solo dispositivos

	// Tabla perfiles - dispositivos
	static final String P_ID_PROFILE = BaseColumns._ID;
	static final String P_GROUP = "dom_group";
	static final String P_DEVICE = "dom_device";
	static final String P_STATE = "dom_estate";
	static final String[] DB_TEXT_COLUMNS = { P_GROUP };

	// Tabla perfiles
	static final String PF_ID_PROFILE = BaseColumns._ID;
	static final String PF_GROUP = "pro_group";
	static final String PF_NAME = "pro_name";
	static final String PF_CREATOR = "pro_creator";
	static final String[] PF_TEXT = { PF_GROUP, PF_NAME, PF_CREATOR };

	// Tabla dispositivos
	static final String D_ID = BaseColumns._ID;
	static final String D_CATEGORY = "dev_category";
	static final String D_TYPE = "dev_type";
	static final String D_VALUES = "dev_values";
	static final String D_MIN = "dev_min";
	static final String D_MAX = "dev_max";
	static final String D_ZONE = "dev_zone";
	static final String D_DEVICE_ID = "dev_device_id";
	static final String D_DEVICE_NAME = "dev_device_name";

	static final String[] DB_DEVICES_COLUMNS = { D_DEVICE_ID };

	static final String[] DB_DEVICES_NAME_COLUMNS = { D_DEVICE_NAME };

	Context context;
	private SQLiteDatabase db; // REPRESENTACION DE LA BASE DE DATOS

	public AndromoticDbHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {

		// TABLA DISPOSITIVOS
		String sqlDevices = "create table if not exists " + TABLE_DEVICES
				+ " (" + D_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ D_DEVICE_NAME + " text, " + D_CATEGORY + " int, " + D_TYPE
				+ " int, " + D_VALUES + " int, " + D_MIN + " int, " + D_MAX
				+ " int, " + D_ZONE + " int, " + D_DEVICE_ID + " int)"; //
		db.execSQL(sqlDevices);
		Log.d(TAG, "onCreated sql: " + sqlDevices);

		// TABLA PERFILES
		String sqlProfiles = "create table if not exists "
				+ TABLE_PROFILES + " (" + PF_ID_PROFILE
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + PF_GROUP + " int, "
				+ PF_NAME + " text, " + PF_CREATOR + " text)"; //
		db.execSQL(sqlProfiles);
		Log.d(TAG, "onCreated sql: " + sqlProfiles);

		// TABLA PERFILES - DISPOSITIVOS
		String sqlProfilesDevices = "create table if not exists "
				+ TABLE_PROFILES_DEVICES + " (" + P_ID_PROFILE
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + P_GROUP + " int, "
				+ P_DEVICE + " int, " + P_STATE + " int)"; //
		db.execSQL(sqlProfilesDevices);
		Log.d(TAG, "onCreated sql: " + sqlProfilesDevices);
	}

	// Called whenever newVersion != oldVersion
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //
		db.execSQL("drop table if exists " + TABLE_DEVICES); // drops the old
		db.execSQL("drop table if exists " + TABLE_PROFILES); // drops the old
		db.execSQL("drop table if exists " + TABLE_PROFILES_DEVICES); // drops
																		// the
																		// old
		Log.d(TAG, "onUpdated");
		onCreate(db); // run onCreate to get new database
	}

	// REGISTRO DE ELEMENTOS
	/**
	 * Agregar dispositivo
	 * 
	 * @return void
	 */
	public void setDevice(IOIODevice device) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			// armamos el paquete para agregar a la BD
			ContentValues values = new ContentValues();
			values.clear();
			values.put(D_DEVICE_NAME, device.getNombre());
			values.put(D_CATEGORY, device.getCategoria());
			values.put(D_TYPE, device.getTipo());
			values.put(D_VALUES, device.getValores());
			values.put(D_MIN, device.getMin_val());
			values.put(D_MAX, device.getMax_val());
			values.put(D_ZONE, device.getZone());
			values.put(D_DEVICE_ID, device.getId());

			try {
				db.insertOrThrow(TABLE_DEVICES, null, values); //
				Log.d("Created device: " + TAG,
						String.format("%s: %s %s", "", device.getId(),
								device.getState()));
			} catch (SQLException e) {
				// Ignore exception
			}

			// Close the database
			db.close(); //
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	/**
	 * Agregar prefiles
	 * 
	 * @return void
	 */
	public void setProfile(AndromoticProfile profile) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			// armamos el paquete para agregar a la BD
			ContentValues values = new ContentValues();
			values.clear();
			values.put(PF_NAME, profile.getNombre());
			values.put(PF_GROUP, profile.getId());
			values.put(PF_CREATOR, profile.getCreador());

			try {
				db.insertOrThrow(TABLE_PROFILES, null, values); //
				Log.d("Created device: " + TAG,
						String.format("%s: %s %s", "", profile.getId(),
								profile.getCreador()));
			} catch (SQLException e) {
				// Ignore exception
			}

			// Close the database
			db.close(); //
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	/**
	 * Agregar dispositivo a un grupo/perfil especifico
	 * 
	 * @return void
	 */
	public void setDeviceOnProfile(AndromoticProfile group, IOIODevice disp) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			ContentValues values = new ContentValues();
			values.clear();
			values.put(P_GROUP, group.getId());
			values.put(P_DEVICE, disp.getId());
			values.put(P_STATE, disp.getState());
			try {
				db.insertOrThrow(TABLE_PROFILES_DEVICES, null, values); //
				Log.d("Se almaceno: " + TAG, String.format("%s: %s %s",
						group.getId(), disp.getId(), disp.getState()));
			} catch (SQLException e) { //
				// Ignore exception
			}
			// Close the database
			db.close(); //
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	// ELIMINACION
	/**
	 * Para eliminar perfiles
	 * 
	 * @return void
	 */
	public void setRemoveProfile(AndromoticProfile profile) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			try {
				db.delete(TABLE_PROFILES,
						PF_NAME + "='" + profile.getNombre() + "'", null);
			} finally {
				db.close();
			}
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	/**
	 * Para eliminar en la base de datos un dispositivo
	 * 
	 * @return void
	 */
	public void setRemoveDevice(IOIODevice device) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			try {
				db.delete(TABLE_DEVICES, D_DEVICE_ID + "='" + device.getId()
						+ "'", null);
			} finally {
				db.close();
			}
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	public void setRemoveDeviceById(int id) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			try {
				db.delete(TABLE_DEVICES, D_DEVICE_ID + "='" + id + "'", null);
			} finally {
				db.close();
			}
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	public void setRemoveDeviceByName(String name) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			try {
				db.delete(TABLE_DEVICES, D_DEVICE_NAME + "='" + name + "'",
						null);
			} finally {
				db.close();
			}
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}

	}

	/**
	 * Para eliminar en la base de datos un dispositivo de un perfil
	 * 
	 * @return void
	 */
	public void setRemoveDeviceFromProfile(IOIODevice device,
			AndromoticProfile profile) {
		try {
			// Open the database for writing
			db = getWritableDatabase();
			try {
				db.delete(TABLE_PROFILES_DEVICES,
						P_DEVICE + "='" + device.getId() + "' AND "
								+ P_ID_PROFILE + "='" + profile.getId() + "'",
						null);
			} finally {
				db.close();
			}
			Log.d(TAG, "Updater ran");
			Thread.sleep(100);
		} catch (InterruptedException e) {

		}
	}

	// CONSULTAS A LA BASE DE DATOS

	public ArrayList<Integer> getDevicesByZone(int zone) {

		SQLiteDatabase db = getReadableDatabase();
		ArrayList<Integer> retorno = new ArrayList<Integer>();

		try {
			Cursor cursor = db.query(TABLE_DEVICES, DB_DEVICES_COLUMNS, D_ZONE
					+ "='" + zone + "'", null, null, null, null);
			try {
				while (cursor.moveToNext()) {
					retorno.add(cursor.getInt(0));
				}
				for (int i = 0; i < retorno.size(); i++) {
					Log.v("Desde la BD  ", retorno.get(i).toString());
				}
				return retorno;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}
	
	public ArrayList<AndromoticProfile> getAndromoticProfiles() {
		SQLiteDatabase db = getReadableDatabase();
		ArrayList<AndromoticProfile> perfileses = new ArrayList<AndromoticProfile>();
		try {
			  Cursor cursor = db.query(TABLE_PROFILES, PF_TEXT, null , null, null, null, null);
			  
			Log.v("PERFILES -----> ", "AUXILIO!! " + cursor.getCount());
			try {
				while (cursor.moveToNext()) {
					AndromoticProfile perTemp = new AndromoticProfile();
					perTemp.setId(cursor.getInt(0));
					perTemp.setNombre(cursor.getString(1));
					perTemp.setCreador(cursor.getString(2));
					perfileses.add(perTemp);
					Log.v("PERFILES -----> ", "EPA!!" + perTemp.getNombre());
					//PF_GROUP, PF_NAME, PF_CREATOR
				}
				return perfileses;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

	public ArrayList<IOIODevice> getIOIODevicesByZone(int zone) {
		SQLiteDatabase db = getReadableDatabase();
		ArrayList<Integer> identificadores = new ArrayList<Integer>();
		ArrayList<IOIODevice> deviceses = new ArrayList<IOIODevice>();
		try {
			Cursor cursor = db.query(TABLE_DEVICES, DB_DEVICES_COLUMNS, D_ZONE
					+ "='" + zone + "'", null, null, null, null);
			try {
				while (cursor.moveToNext()) {
					identificadores.add(cursor.getInt(0));
				}
				for (int i = 0; i < identificadores.size(); i++) {
					deviceses.add(getIOIODeviceById(identificadores.get(i)
							.intValue()));
					Log.v("Desde la BD ids:  ", identificadores.get(i)
							.toString());
				}
				return deviceses;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

	public String getDeviceNameById(int id) {

		SQLiteDatabase db = getReadableDatabase();
		String retorno = "no name";

		try {
			Cursor cursor = db.query(TABLE_DEVICES, DB_DEVICES_NAME_COLUMNS,
					D_DEVICE_ID + "='" + id + "'", null, null, null, null);
			try {
				if (cursor.moveToNext()) {
					retorno = cursor.getString(0);
					Log.v("Desde la BD  ", retorno);
				}
				return retorno;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

	public IOIODevice getIOIODeviceById(int id) {

		SQLiteDatabase db = getReadableDatabase();
		String[] retorno = new String[5];
		String nombre = "no name";
		int zone = -1;

		String[] daticos = { D_CATEGORY, D_TYPE, D_VALUES, D_MIN, D_MAX,
				D_ZONE, D_DEVICE_NAME };

		try {
			Cursor cursor = db.query(TABLE_DEVICES, daticos, D_DEVICE_ID + "='"
					+ id + "'", null, null, null, null);
			try {
				if (cursor.moveToNext()) {
					for (int i = 0; i < retorno.length; i++) {
						retorno[i] = cursor.getString(i);
						Log.v("Desde la BD  ", retorno[i]);
					}

					zone = cursor.getInt(5);
					nombre = cursor.getString(6);

				}
				return createdDevice(retorno, zone, nombre, id);
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

	private IOIODevice createdDevice(String[] datos, int zone, String nombre,
			int id2) {

		int tipo = Integer.parseInt(datos[0]);
		int in_out = Integer.parseInt(datos[1]);
		int valores = Integer.parseInt(datos[2]);
		int min_val = Integer.parseInt(datos[3]);
		int max_val = Integer.parseInt(datos[4]);
		int state = 0;
		int id = id2;

		return new IOIODevice(tipo, in_out, valores, min_val, max_val, state,
				id, zone, nombre);
	}

	public ArrayList<Integer> getDevicesByProfile(AndromoticProfile profile) {

		SQLiteDatabase db = getReadableDatabase();
		ArrayList<Integer> retorno = new ArrayList<Integer>();

		try {
			Cursor cursor = db.query(TABLE_PROFILES_DEVICES, DB_TEXT_COLUMNS,
					P_GROUP + "='" + profile.getId() + "'", null, null, null,
					null);
			try {
				while (cursor.moveToNext()) {
					retorno.add(cursor.getInt(0));
				}
				for (int i = 0; i < retorno.size(); i++) {
					Log.v("Desde la BD  ", retorno.get(i).toString());
				}
				return retorno;
			} finally {
				cursor.close();
			}
		} finally {
			db.close();
		}
	}

}
